/* 
 * Copyright 2010 Ziya Guven Koylu, Ozge Koylu
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at 
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 */
package com.koylu.caffein.query;

import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.koylu.caffein.model.caffein.CaffeinConfig;
import com.koylu.caffein.model.clazz.Clazz;
import com.koylu.caffein.model.clazz.Column;
import com.koylu.caffein.query.order.Order;
import com.koylu.caffein.query.target.Target;

public class MsSqlQuery extends Query {

	private StringBuffer query = null;
	
	public MsSqlQuery(CaffeinConfig caffeinConfig, Clazz clazz, String alias) {
		super(caffeinConfig, clazz, alias);
	}
	
	public Query addOrder(Order order) throws Exception {
		query = null;
		return super.addOrder(order);
	}
	
	public StringBuffer orderBy() throws Exception {
		List<Order> orders = getOrders();
		if(orders != null){
			if (orders.size() > 0) {
				if(query == null){
					query = new StringBuffer(" ORDER BY ");
					for (Iterator<Order> iterator = orders.iterator(); iterator.hasNext();) {
						Order order = iterator.next();
						String columnAlias = order.getColumnAlias(); 
						if(columnAlias == null){
							Column column = null;
							Map<String,Clazz> aliasMap = getAliasMap();
							String alias = order.getAlias();
							String path = order.getPath();
							if(aliasMap.containsKey(alias)){
								column = aliasMap.get(alias).pathToColumn(getCaffeinConfig(), path);
								if(column == null){
									throw new RuntimeException("invalid clazz path:"+path);
								}
							}else{
								throw new RuntimeException("invalid clazz alias:"+alias);
							}
							query.append(column.getColumn()).append(" ").append(order.getType());						
						}else{
							query.append(columnAlias).append(" ").append(order.getType());
						}
						if (iterator.hasNext()) {
							query.append(", ");
						}
					}	
				}
				return query;	
			}			
		}
		return emptyStringBuffer;
	}
	
	public StringBuffer listQuery(CaffeinConfig caffeinConfig, Map<String, Clazz> aliasMap, Target target, int firstResult, int maxResult) throws Exception {
		List<Order> orders = getOrders();
		if(orders == null || orders.size() < 1){
			throw new RuntimeException("add an order to the query");
		}
		/*
		SELECT * FROM(
		    SELECT caffein_row_numbered_table.*,((ROW_NUMBER() OVER ( ORDER BY name ASC)))-1 caffein_row_number FROM (
		        SELECT usr.NAME, COUNT(*) role_count FROM OIM_TEST_USR usr 
		        INNER JOIN OIM_TEST_USR_ROL OIM_TEST_USR_ROL ON usr.id = OIM_TEST_USR_ROL.usr_id 
		        INNER JOIN OIM_TEST_ROL rol ON OIM_TEST_USR_ROL.rol_id = rol.id GROUP BY usr.NAME
		    ) caffein_row_numbered_table
		) caffein_merged_table where caffein_row_number < 6 and caffein_row_number >= 0
		*/
		StringBuffer query = new StringBuffer("SELECT * FROM(SELECT caffein_row_numbered_table.*,((ROW_NUMBER() OVER (").append(orderBy()).append(")))-1 caffein_row_number FROM (")
		.append("SELECT ").append(target.toQuery(caffeinConfig, aliasMap)).append(fromTables()).append(whereClause()).append(groupBy())
		.append(") caffein_row_numbered_table) caffein_merged_table where caffein_row_number < ")
		.append(firstResult + maxResult).append(" AND caffein_row_number >= ").append(firstResult);
		return query;
	}
}